package com.learning.platform.repository;

import com.learning.platform.entity.CourseReview;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Optional;

@Repository
public interface CourseReviewRepository extends JpaRepository<CourseReview, Long> {
    
    /**
     * 查找用户对课程的评价
     */
    Optional<CourseReview> findByUserIdAndCourseId(Long userId, Long courseId);
    
    /**
     * 检查用户是否已评价课程
     */
    boolean existsByUserIdAndCourseId(Long userId, Long courseId);
    
    /**
     * 获取课程的评价列表，按时间倒序
     */
    Page<CourseReview> findByCourseIdOrderByCreatedAtDesc(Long courseId, Pageable pageable);
    
    /**
     * 计算课程平均评分
     */
    @Query("SELECT AVG(r.rating) FROM CourseReview r WHERE r.courseId = :courseId")
    Optional<BigDecimal> findAverageRatingByCourseId(@Param("courseId") Long courseId);
    
    /**
     * 获取课程评价总数
     */
    long countByCourseId(Long courseId);
    
    /**
     * 获取课程各评分的数量统计
     */
    @Query("SELECT r.rating, COUNT(r) FROM CourseReview r WHERE r.courseId = :courseId GROUP BY r.rating ORDER BY r.rating DESC")
    Object[][] findRatingStatsByCourseId(@Param("courseId") Long courseId);
    
    /**
     * 删除课程的所有评价
     */
    void deleteByCourseId(Long courseId);
}



